<?php
header("Content-Type:text/html;charset=utf-8"); 
include '../dbConfig/mysqlFun.php';
require_once 'PHPExcel.php';

if(isset($_POST['exportExcelModelSubmit'])){
	if(isset($_POST['tableName'])&&!empty($_POST['tableName'])){
		foreach($_POST['tableName'] as $tableName){
			exportExcelModel($tableName);
		}
	}
}

if(isset($_POST['exportDBDateSubmit'])){
	if(isset($_POST['tableName'])&&!empty($_POST['tableName'])){
		foreach($_POST['tableName'] as $tableName){
			$sql = "SELECT * FROM ".$tableName;
			exportDBDate($sql,$tableName);
		}
	}
}

function exportExcelModel($tableName){
	$mysqlDB = new mysqlFun();
	
	$tableField = $mysqlDB->showTableFULLFIELDS($tableName);
	
	$excelNum = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
	$tableFieldNum = count($tableField)-1;
	$objExcel = new PHPExcel();
	$objExcel->setActiveSheetIndex(0);  
	$objActSheet = $objExcel->getActiveSheet();  
	$objActSheet->setTitle('article');
	$objActSheet->setCellValue('A1', $tableName);
	$objActSheet->mergeCells("A1:".$excelNum[$tableFieldNum]."1"); 
	$objStyleA1 = $objActSheet->getStyle('A1'); 
	//设置字体  
	$objFontA1 = $objStyleA1->getFont();  
	$objFontA1->setName('Courier New');  
	$objFontA1->setSize(15);  
	$objFontA1->setBold(true);  
	//$objFontA1->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);  
	//$objFontA1->getColor()->setARGB('FF999999'); 
	
	//设置对齐方式  
	$objAlignA1 = $objStyleA1->getAlignment();  
	$objAlignA1->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
	$objAlignA1->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
	
	foreach($tableField as $key=>$fieldName){
		if($key!=0){
			$excelNumKey = $key-1;
			$objActSheet->setCellValue($excelNum[$excelNumKey]."2",$fieldName);
			//$objActSheet->getColumnDimension($excelNum[$key])->setAutoSize(true); 
			$objActSheet->getColumnDimension($excelNum[$excelNumKey])->setWidth(30); 
		}
	}
	
	$articleName = $tableName.".xls";
	$objWriter = new PHPExcel_Writer_Excel5($objExcel);
	
	//$objWriter->save($articleName);
	
	header("Content-Type: application/force-download");  
	header("Content-Type: application/octet-stream");  
	header("Content-Type: application/download");  
	header('Content-Disposition:inline;filename="'.$articleName.'"');  
	header("Content-Transfer-Encoding: binary");  
	header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");  
	header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");  
	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
	header("Pragma: no-cache");  
	$objWriter->save('php://output');
}

function exportDBDate($sql,$tableName){
	$mysqlDB = new mysqlFun();
	
	$tableField = $mysqlDB->showTableFULLFIELDS($tableName);
	$dbDate = $mysqlDB->find($sql);
	
	$excelNum = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
	$tableFieldNum = count($tableField)-1;
	$objExcel = new PHPExcel();
	$objExcel->setActiveSheetIndex(0);  
	$objActSheet = $objExcel->getActiveSheet();  
	$objActSheet->setTitle($tableName);
	$objActSheet->setCellValue('A1', $tableName);
	$objActSheet->mergeCells("A1:".$excelNum[$tableFieldNum]."1"); 
	$objStyleA1 = $objActSheet->getStyle('A1'); 
	//设置字体  
	$objFontA1 = $objStyleA1->getFont();  
	$objFontA1->setName('Courier New');  
	$objFontA1->setSize(15);  
	$objFontA1->setBold(true);  
	//$objFontA1->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);  
	//$objFontA1->getColor()->setARGB('FF999999'); 
	
	//设置对齐方式  
	$objAlignA1 = $objStyleA1->getAlignment();  
	$objAlignA1->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
	$objAlignA1->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
	
	$dbDateBeginNum = 3;//表数据开始插入excel的行号
	
	/*$excelHead = array();//测试excel头部信息
	$excelBody = array();//测试excel表数据信息*/
		
	foreach($dbDate as $dbValue){
		foreach($tableField as $key=>$fieldName){
			if($key!=0){
				//输出excel头部信息部分
				$excelNumKey = $key-1;
				$objActSheet->setCellValue($excelNum[$excelNumKey]."2",$fieldName);
				//$objActSheet->getColumnDimension($excelNum[$excelNumKey])->setAutoSize(true); 
				$objActSheet->getColumnDimension($excelNum[$excelNumKey])->setWidth(30);
				
				//$excelHead[$excelNum[$excelNumKey]."2"] = $fieldName;//测试输出excel头部信息
				
				//输出excel的表数据部分
				$tableFieldName = explode("(",$fieldName);
        		$sqlField = $tableFieldName[0];
				$objActSheet->setCellValue($excelNum[$excelNumKey].$dbDateBeginNum,$dbValue[$sqlField]);
				
				//$excelBody[$excelNum[$excelNumKey].$dbDateBeginNum] = $dbValue[$sqlField];//测试输出excel表数据信息
			}
		}
		$dbDateBeginNum ++; 
	}
	
	/*var_dump($excelHead);
	var_dump($excelBody);*/
	
	$articleName = $tableName.".xls";
	$objWriter = new PHPExcel_Writer_Excel5($objExcel);
	
	//$objWriter->save($articleName);
	
	header("Content-Type: application/force-download");  
	header("Content-Type: application/octet-stream");  
	header("Content-Type: application/download");  
	header('Content-Disposition:inline;filename="'.$articleName.'"');  
	header("Content-Transfer-Encoding: binary");  
	header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");  
	header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");  
	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
	header("Pragma: no-cache");  
	$objWriter->save('php://output');
}

?>
<br />
<a href="mysqlAllTable.php" alt="">返回</a>













